﻿using System;
using System.Data.SqlClient;
using System.Data;

namespace DBConnection
{
    public class DBConnection
    {
        private SqlConnection _connection;
        private String _user;
        private String _password;
        private String _dbName;
        private DataSet _ds;

        public DBConnection(String source, String user, String password, String db)
        {
            if (source == String.Empty || user == String.Empty || db == String.Empty)
                throw new Exception("Connection error!");

            _user = user;
            _password = password;
            _dbName = db;
            String connString = "Data Source=" + source + ";User ID=" + _user + ";Password=" + _password + ";Initial Catalog=" + _dbName + ";Integrated Security=false";

            try
            {
                _connection = new SqlConnection(connString);
                _connection.Open();
                _connection.Close();
                _ds = new DataSet();
            }
            catch (Exception exc)
            {
                throw new Exception("Error connect to your database! Please check your configuration info!");
            }
        }

        public int sp_ExecNonQuery(String spName, String[] parametersArr, String[] parametersValue)
        {
            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
            sqlCommand.Connection = _connection;
            sqlCommand.CommandText = spName;
            for (int i = 0; i < parametersArr.Length; i++)
            {
                sqlCommand.Parameters.Add(parametersArr[i], System.Data.SqlDbType.NVarChar);
                sqlCommand.Parameters[parametersArr[i]].Value = parametersValue[i];
            }

            try
            {
                _connection.Open();
                return sqlCommand.ExecuteNonQuery();
            }
            catch (Exception exc)
            {
                throw new Exception("Error executing stored procedure");
            }
            finally
            {
                _connection.Close();
            }
        }

        public int sp_ExecScalar(String spName, String[] parametersArr, String[] parametersValue)
        {
            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
            sqlCommand.Connection = _connection;
            sqlCommand.CommandText = spName;
            for (int i = 0; i < parametersArr.Length; i++)
            {
                sqlCommand.Parameters.Add(parametersArr[i], System.Data.SqlDbType.NVarChar);
                sqlCommand.Parameters[parametersArr[i]].Value = parametersValue[i];
            }

            try
            {
                if (_connection.State == ConnectionState.Closed)
                    _connection.Open();
                return sqlCommand.ExecuteNonQuery();
            }
            catch (Exception exc)
            {
                throw new Exception("Error executing stored procedure");
            }
            finally
            {
                _connection.Close();
            }
        }

        public DataTable sp_ExecReader(String spName, String tbName, String[] parametersArr, String[] parametersValue)
        {
            if (_ds.Tables.IndexOf(tbName) != -1)
            {
                _ds.Tables.Remove(tbName);
            }

            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
            sqlCommand.Connection = _connection;
            for (int i = 0; i < parametersArr.Length; i++)
            {
                sqlCommand.Parameters.Add(parametersArr[i], System.Data.SqlDbType.NVarChar);
                sqlCommand.Parameters[parametersArr[i]].Value = parametersValue[i];
            }

            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            try
            {
                if (_connection.State == ConnectionState.Closed)
                    _connection.Open();
                da.Fill(_ds, tbName);
                return _ds.Tables[tbName];
            }
            catch (Exception exc)
            {
                throw new Exception("Error executing stored procedure");
            }
            finally
            {
                _connection.Close();
            }
        }

        public DataTable sp_ExecReader(String spName, String tbName)
        {
            if (_ds.Tables.IndexOf(tbName) != -1)
            {
                _ds.Tables.Remove(tbName);
            }

            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
            sqlCommand.CommandText = spName;
            sqlCommand.Connection = _connection;

            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            try
            {
                if (_connection.State == ConnectionState.Closed)
                    _connection.Open();
                da.Fill(_ds, tbName);
                return _ds.Tables[tbName];
            }
            catch (Exception exc)
            {
                throw new Exception("Error executing stored procedure");
            }
            finally
            {
                _connection.Close();
            }
        }

        public DataTable getDataTable(String sqlSelect, String tbName)
        {
            if (_ds.Tables.IndexOf(tbName) != -1)
            {
                _ds.Tables.Remove(tbName);
            }
            try
            {
                _connection.Open();
                SqlDataAdapter adapter = new SqlDataAdapter(sqlSelect, _connection);
                adapter.Fill(_ds, tbName);
            }
            catch (SqlException exc)
            {
                throw exc;
            }
            finally
            {
                _connection.Close();
            }
            return _ds.Tables[tbName];
        }
    }
}
